package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import dao.WareDao;
import entity.Ware;
import util.DBUtils;

public class WareDaoImpl implements WareDao {

	@Override
	public boolean insert(Ware ware) {
		try(Connection c = DBUtils.getConnection()){
			String sql= "insert into ware values(?,?,?,?,?,?,?,?)";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, ware.getWareid());
			ps.setString(2, ware.getWarename());
			ps.setString(3, ware.getAbbreviation());
			ps.setString(4, ware.getProdarea());
			ps.setString(5, ware.getSpecification());
			ps.setString(6, ware.getBatchnum());
			ps.setString(7, ware.getSuppid());
			ps.setString(8, ware.getRemark());
			return ps.executeUpdate() == 1 ? true: false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean delete(String wareid) {
		try(Connection c = DBUtils.getConnection()){
			String sql= "delete from ware where wareid = ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, wareid);
			return ps.executeUpdate() == 1 ? true: false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(Ware ware) {
		try(Connection c = DBUtils.getConnection()){
			String sql= "update ware set warename = ?,abbreviation = ?,prodarea = ?,"
					+ " specification = ?,batchnum = ?,suppid = ?,remark =? where wareid = ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(8, ware.getWareid());
			ps.setString(1, ware.getWarename());
			ps.setString(2, ware.getAbbreviation());
			ps.setString(3, ware.getProdarea());
			ps.setString(4, ware.getSpecification());
			ps.setString(5, ware.getBatchnum());
			ps.setString(6, ware.getSuppid());
			ps.setString(7, ware.getRemark());
			return ps.executeUpdate() == 1 ? true: false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public List<Ware> selectByName(String warename) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "select wareid,warename,abbreviation,prodarea,specification,batchnum,suppid,remark"
					+ " from ware where warename like ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, "%"+warename+"%");
			// 执行
			ResultSet rs = ps.executeQuery();
			List<Ware> wares = new ArrayList<>();
			while (rs.next()) {
				Ware w = new Ware();
				w.setWareid(rs.getString(1));
				w.setWarename(rs.getString(2));
				w.setAbbreviation(rs.getString(3));
				w.setProdarea(rs.getString(4));
				w.setSpecification(rs.getString(5));
				w.setBatchnum(rs.getString(6));
				w.setSuppid(rs.getString(7));
				w.setRemark(rs.getString(8));
				wares.add(w);
			}
			return wares.isEmpty() ? Collections.emptyList(): wares;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	@Override
	public List<Ware> selectBySuppname(String suppname) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "select wareid,warename,abbreviation,prodarea,specification,batchnum,suppid,remark"
					+ " from ware left join supp using(suppid) "
					+ " where suppname=?";
			
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, suppname);
			// 执行
			ResultSet rs = ps.executeQuery();
			List<Ware> wares = new ArrayList<>();
			while (rs.next()) {
				Ware w = new Ware();
				w.setWareid(rs.getString(1));
				w.setWarename(rs.getString(2));
				w.setAbbreviation(rs.getString(3));
				w.setProdarea(rs.getString(4));
				w.setSpecification(rs.getString(5));
				w.setBatchnum(rs.getString(6));
				w.setSuppid(rs.getString(7));
				w.setRemark(rs.getString(8));
				wares.add(w);
			}
			return wares.isEmpty() ? Collections.emptyList(): wares;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

}
